package com.simon.customer.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.simon.customer.dao.IDao;
import com.simon.customer.dbutil.DBUtil;
import com.simon.customer.entity.DishType;
import com.simon.customer.entity.Dishes;
import com.simon.customer.entity.MerchantView;

public class Dao implements IDao {

	@Override
	// 商家信息
	public MerchantView searchMerchant(String mId) {
		String sql = "select * from merchant where merchant_Id=?";
		Connection conn = DBUtil.getConnection();
		MerchantView mv = new MerchantView();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				mv.setMerchantId(rs.getString(1));
				mv.setMerchantName(rs.getString(2));
				mv.setMerchantAddress(rs.getString(4));
				mv.setMerchantPic(rs.getString(5));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return mv;
	}

	@Override
	// 商家所有菜
	public List<Dishes> searchDishes(String m_id) {
		String sql = "select distinct food_Name,food_Price,food_Pic,type_Name,merchant_Name from foods,food_types,merchant where foods.food_Type_Id=food_types.type_Id and foods.merchant_Id=merchant.merchant_Id and merchant.merchant_Id=?";
		Connection conn = DBUtil.getConnection();
		List<Dishes> dishes = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, m_id);
			ResultSet rs = ps.executeQuery();
			Dishes d = null;
			while (rs.next()) {
				String foodName = rs.getString(1);
				String foodPrice = rs.getString(2);
				String foodPic = rs.getString(3);
				String typeName = rs.getString(4);
				String merchantName = rs.getString(5);
				d = new Dishes(foodName, foodPrice, foodPic, typeName,
						merchantName);
				if(d!=null){
				dishes.add(d);
				}
				
			}

			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return dishes;
	}

	@Override
	// 某个商家的某种菜品
	public List<Dishes> searchDishesTypeName(String mId, String typeId) {
		String sql = "select food_Name,food_Price,food_Pic,type_Name,merchant_Name"
				+ "  from foods,food_types,merchant where foods.food_Type_Id=food_types.type_Id "
				+ "and foods.merchant_Id=merchant.merchant_Id and type_Id=? "
				+ "and foods.merchant_Id=?";
		Connection conn = DBUtil.getConnection();
		List<Dishes> dishes = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, typeId);
			ps.setString(2, mId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String foodName = rs.getString(1);
				String foodPrice = rs.getString(2);
				String foodPic = rs.getString(3);
				String typeName=rs.getString(4);
				String merchantName = rs.getString(5);
				dishes.add(new Dishes(foodName, foodPrice, foodPic, typeName,
						merchantName));
			}
			conn.close();
		} catch (SQLException e) {

			e.printStackTrace();
		}

		return dishes;
	}

	@Override
	// 某种类的所有菜
	public List<Dishes> searchAllDishesByType(String foodTypeName) {
		String sql = "select foodName,foodPrice,foodPic,typeName,merchantName "
				+ "from foods,food_types,merchant where "
				+ "foods.food_Type_Id=food_types.type_Id and "
				+ "foods.merchant_Id=merchant.merchant_Id  and type_Name=?";
		Connection conn = DBUtil.getConnection();
		List<Dishes> dishes = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, foodTypeName);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String foodName = rs.getString(1);
				String foodPrice = rs.getString(2);
				String foodPic = rs.getString(3);
				String typeName = rs.getString(4);
				String merchantName = rs.getString(5);
				dishes.add(new Dishes(foodName, foodPrice, foodPic, typeName,
						merchantName));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	// 所有菜品
	public List<DishType> searchAlldishType() {
		String sql = "select * from food_types";
		Connection conn = DBUtil.getConnection();
		List<DishType> list = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				list.add(new DishType(rs.getString(1), rs.getString(2)));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}

	@Override
	// 找某个品种的商家
	public List<MerchantView> searchMerchantByDishType(String t_id) {
		String sql = "select distinct merchant.merchant_Id,merchant_Name,merchant_Address,merchant_Pic from foods,merchant "
				+ "where merchant.merchant_Id=foods.merchant_Id and food_Type_Id=?";
		Connection conn = DBUtil.getConnection();
		List<MerchantView> list = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, t_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String merchantId = rs.getString(1);
				String merchantName = rs.getString(2);
				String merchantAddress = rs.getString(3);
				String merchantPic = rs.getString(4);
				list.add(new MerchantView(merchantId, merchantName,
						merchantAddress, merchantPic));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override//过滤无菜商家      由商家id查品种
	public List<DishType> searchaDishTypeByMid(String mId) {
		String sql = "select distinct type_Id,type_Name from food_types,foods where FOOD_TYPES.type_Id=foods.food_Type_Id and merchant_Id=?";
		Connection conn = DBUtil.getConnection();
		List<DishType> list = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				list.add(new DishType(rs.getString(1), rs.getString(2)));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;
	}
	//获取商家名字
	public String getMerchantName(String mId) {
		String sql = "select merchant_Name from merchant where merchant_Id=?";
		Connection conn = DBUtil.getConnection();
		String str = null;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			ResultSet rs = ps.executeQuery();
			if (rs.next()) {
				str = rs.getString(1);
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return str;
	}

	@Override//通过品种和商家id查商家的此品种所有菜
	public List<Dishes> searchAlldishTypeId(String t_id, String m_id) {
		String sql = "select distinct food_Name,food_Price,food_Pic,type_Name,merchant_Name "
				+ "from foods,merchant,food_types where  foods.food_Type_Id="
				+ "food_types.type_Id and"
				+ " foods.merchant_Id=merchant.merchant_Id and   foods.merchant_id=? and food_type_id=?";
		Connection conn = DBUtil.getConnection();
		List<Dishes> list = new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, m_id);
			ps.setString(2, t_id);
			ResultSet rs = ps.executeQuery();
			while (rs.next()) {
				String foodName = rs.getString(1);
				String foodPrice = rs.getString(2);
				String foodPic = rs.getString(3);
				String typeName = rs.getString(4);
				String merchantName = rs.getString(5);
				list.add(new Dishes(foodName, foodPrice, foodPic, typeName,
						merchantName));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}

		return list;

	}

	@Override//查通过商家的菜品列表
	public List<DishType> searchDishTypeByMId(String mId) {
		String sql = "select distinct type_id,type_name from food_types,foods where food_types.type_id=foods.food_type_id and merchant_id=?";
		Connection conn = DBUtil.getConnection();
		List<DishType> type=new ArrayList<>();
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			ResultSet rs = ps.executeQuery();
			while(rs.next())
			{
				String typeId= rs.getString(1);
				String typeName=rs.getString(2);
				type.add(new DishType(typeId,typeName));
			}
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return type;
	}

	@Override
	public boolean searchMerchantInFood(String mId) {
		String sql = "select * from foods where merchant_id=?";
		Connection conn = DBUtil.getConnection();
		boolean bo = false;
		try {
			PreparedStatement ps = conn.prepareStatement(sql);
			ps.setString(1, mId);
			ResultSet rs = ps.executeQuery();
			bo=rs.next();
			conn.close();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return bo;
	}

}
